package cn.mgm.advanced;

import cn.mgm.advanced.pojo.SysRole;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCAdvancedTest {


    private final String url="jdbc:mysql://139.224.197.217:3336/guigu-auth";

    private final String username="root";

    private final String password="kaa3akv8";
    @Test
    public void testSingleORM() throws SQLException {
        // 注册驱动
        // 获取连接
        Connection connection = DriverManager.getConnection(url, username, password);
        // 预设 SQL
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT id,role_name,role_code,description FROM sys_role where id=?");
        // 赋值并执行
        preparedStatement.setInt(1,1);
        // 处理结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        SysRole sysRole =null;
        if (resultSet.next()){

            Long id= resultSet.getLong("id");
            String role_name = resultSet.getString("role_name");
            String role_code = resultSet.getString("role_code");
            String description = resultSet.getString("description");

            sysRole= new SysRole(id,role_name,role_code,description);
        }
        System.out.println(sysRole);
        // 资源释放
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void multiORM() throws SQLException {
        // 注册驱动
        // 获取连接
        Connection connection = DriverManager.getConnection(url, username, password);
        // 预设 SQL
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT id,role_name,role_code,description FROM sys_role where is_deleted=?");
        // 赋值并执行
        preparedStatement.setInt(1,0);
        // 处理结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        List<SysRole> list = new ArrayList<>();
        while (resultSet.next()){
            Long id= resultSet.getLong("id");
            String role_name = resultSet.getString("role_name");
            String role_code = resultSet.getString("role_code");
            String description = resultSet.getString("description");
            SysRole sysRole= new SysRole(id,role_name,role_code,description);
            list.add(sysRole);
        }
        System.out.println(list);
        // 资源释放
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    @Test
    public void testReturnPk() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO sys_role(role_name, role_code, description) VALUES (?,?,?)",Statement.RETURN_GENERATED_KEYS);

        SysRole sysRole =new SysRole(null,"yushi1","admin","测试");
        preparedStatement.setString(1,sysRole.getRoleName());
        preparedStatement.setString(2,sysRole.getRoleCode());
        preparedStatement.setString(3,sysRole.getDescription());

        int i = preparedStatement.executeUpdate();

        if (i>0){
            System.out.println("成功");
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()){
                long generatedKeysLong = generatedKeys.getLong(1);
                sysRole.setId(generatedKeysLong);
                System.out.println(sysRole);
            }
            generatedKeys.close();
        }else {
            System.out.println("失败");
        }

        preparedStatement.close();
        connection.close();

    }


    /**
     * 批量插入注意
     * 1. 必须在连接的 URL 后面拼?rewriteBatchedStatements=true 允许批量操作
     * 2. 新增 SQL 必须用 values,且语句后不能加分号;
     * 3. 调用 addBatch 方法,将 SQL 语句进行批量操作
     * 4. 统一执行调用executeBatch
     */
    @Test
    public void testBatchInsert() throws SQLException {
        Connection connection = DriverManager.getConnection(url+"?rewriteBatchedStatements=true", username, password);

        String  sql="INSERT INTO sys_role(role_name,role_code, description) VALUES (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long l = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setString(1,"yushi"+i);
            preparedStatement.setString(2,"admin"+i);
            preparedStatement.setString(3,"描述"+i);
            preparedStatement.addBatch();
        }

        preparedStatement.executeBatch();
        long l1 = System.currentTimeMillis();

        System.out.println("消耗时间:"+(l1-l));
        preparedStatement.close();
        connection.close();
    }


}
